/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package data;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import au.com.bytecode.opencsv.CSVReader;
import config.Debug;
import config.Helper;
import config.Settings;

/**
 * 
 * @author Yang Wang <yang@digitalmenubox.com>
 */
public class CSVToMysqlConverter {

	public void put(String csvFile, String tableName) throws IOException {
		assert (csvFile != null && tableName != null);
		List<String> statements = new ArrayList<String>();
		CSVReader reader = null;
		String[] keys = null;
		String[] values = null;
		String sql = "";
		Connection conn = Settings.getLocalDatabaseConnection();
		ResultSet result = null;
		Statement stmt = null;

		if (conn == null) {
			// no connection
			return;
		}
		// creates the reader
		try {
			reader = new CSVReader(new FileReader(csvFile));
			keys = reader.readNext();
			while ((values = reader.readNext()) != null) {
				try {
					sql = String.format("SELECT * FROM %1$s WHERE %2$s=\'%3$s\' LIMIT 1", tableName, keys[0], values[0]);
					stmt = conn.createStatement();
					result = stmt.executeQuery(sql);
					result.last();
					if (result.getRow() == 0) {
						sql = "INSERT INTO " + tableName + " (`" + Helper.implode(keys, "`, `") + "`) VALUES('" + Helper.implode(values, "','") + "');";

						// statements.add(sql);
					} else {
						StringBuilder sb = new StringBuilder();
						sb.append("UPDATE " + tableName + " SET ");
						for (int i = 0; i < keys.length; i++) {
							sb.append("`" + keys[i] + "`='" + values[i] + "',");
						}
						sb.deleteCharAt(sb.length() - 1);
						sb.append(" WHERE " + keys[0] + "='" + values[0] + "';");
						sql = sb.toString();

						// statements.add(sql);
					}
					Helper.localMysqlExecute(sql);
				} catch (SQLException ex) {
					Debug.log("CSVToMysqlConverter.put: SQLException " + ex.getMessage(), Debug.ERROR_LEVEL);
				} finally {
					try {
						if (result != null)
							result.close();
						if (stmt != null)
							stmt.close();
					} catch (SQLException ex) {
						Debug.log("CSVToMysqlConverter.put: SQLException " + ex.getMessage(), Debug.ERROR_LEVEL);
					}

				}
			}
			reader.close();
		} catch (FileNotFoundException ex) {
			Debug.log(CSVToMysqlConverter.class.getName() + ": cannot find CSV file - " + csvFile, Debug.ERROR_LEVEL);
		}
	}

	public static void main(String[] args) throws FileNotFoundException, IOException {
		CSVToMysqlConverter c = new CSVToMysqlConverter();
		c.put("out.csv", "agents");

	}
}
